﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;

namespace FAS.DAL.Utils
{
    public class DbBackup
    {
        string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["db"].ConnectionString;

        public void backup(string database,string file)
        {

            SqlConnection connection = new SqlConnection(connStr);
            if (File.Exists(file)) File.Delete(file);
            SqlCommand command = new SqlCommand("backup database @name to disk=@path;", connection);
            connection.Open();
            command.Parameters.AddWithValue("@name", connection.Database);
            command.Parameters.AddWithValue("@path", file);
            try
            {
                if (File.Exists(file)) File.Delete(file);
                command.ExecuteNonQuery();                
            }
            finally
            {
                connection.Close();               
            }
        }
        public void restore(string dbName, string file)
        {            
            SqlConnection connection = new SqlConnection(connStr);
            connection.ConnectionString = connStr; 
            try
            {
                connection.Open();
                SqlCommand command; 
                #region create procedure KillSpid
                command = new SqlCommand();
                command.Connection=connection;
                command.CommandText = "use master;SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KillSpid]') AND type in (N'P', N'PC')";
                command.CommandType = System.Data.CommandType.Text;
                int n = (int)command.ExecuteScalar();
                if (n == 0)
                {                   
                    command.CommandText = @"  CREATE PROCEDURE KillSpid(@dbName varchar(20))
  AS
  BEGIN
     DECLARE @sql nvarchar(500)
     DECLARE @spid int     
     DECLARE getspid CURSOR FOR SELECT spid FROM sys.sysprocesses WHERE dbid=db_id(@dbName)   
     OPEN getspid
     FETCH next FROM getspid INTO @spid
     WHILE @@fetch_status<>-1
     BEGIN
         EXEC('kill '+@spid)
         FETCH next FROM getspid INTO @spid
     END
     CLOSE getspid
     DEALLOCATE getspid
 END";
                    command.ExecuteNonQuery();
                    
                }
                #endregion
                command.CommandText = "execute KillSpid @dbname";
                command.Parameters.AddWithValue("@dbname", dbName);
                command.ExecuteNonQuery();
                command = new SqlCommand("use master;restore database @name from disk=@path with replace;", connection);
                command.Parameters.AddWithValue("@name", dbName);
                command.Parameters.AddWithValue("@path", file);
                command.ExecuteNonQuery();                
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
